CREATE OR REPLACE PROCEDURE GET_PAYMODE(ORDERTYPE IN VARCHAR2,
                                        PAYSCENE  IN VARCHAR2,
                                        AMOUNT    IN FLOAT,
                                        VERSION   IN INTEGER,
                                        V_RESULT  OUT SYS_REFCURSOR) AS
  PMIDS         VARCHAR2(100);
  PSIDS         VARCHAR2(100);
  CUR_PSID_TEMP VARCHAR2(100);
  CUR_PMID_TEMP VARCHAR2(100);
  V_SQL VARCHAR2(5000);
  ENTRY_CURSOR  SYS_REFCURSOR;
  CURSOR CUR_PSID IS
    select ID
      from o_pay_scens
     where ((instr(order_types, ORDERTYPE) != 0 and
           is_limit_order_types = 1) or is_limit_order_types = 0)
          
       and (IS_LIMIT_PAY_SCENE = 0 or
           (IS_LIMIT_PAY_SCENE = 1 and instr(pay_scenes, PAYSCENE) != 0))
          
       and (IS_LIMIT_AMOUNT = 0 OR
           (IS_LIMIT_AMOUNT = 1 AND AMOUNT > MIN_AMOUNT AND
           AMOUNT <= MAX_AMOUNT))
       and is_show = 1;
BEGIN
  OPEN CUR_PSID;
  LOOP
    FETCH CUR_PSID
      INTO CUR_PSID_TEMP;
    EXIT WHEN CUR_PSID%NOTFOUND;
    PSIDS := PSIDS || CUR_PSID_TEMP || ',';
  
  END LOOP;
  CLOSE CUR_PSID;
  
  IF length(PSIDS) > 0 THEN
    PSIDS := substr(PSIDS,1,length(PSIDS)-1);
    PSIDS := ''||REPLACE(PSIDS, ',', ''',''')||'';
  END IF;
V_SQL:='SELECT PM_ID '||
'FROM O_PAY_ENTRY '||
'WHERE (IS_LIMIT_TIME = 0 OR  (IS_LIMIT_TIME = 1 AND SYSDATE >= START_TIME AND  SYSDATE <= END_TIME)) ' ||
'AND (IS_LIMIT_VERSION = 0 OR (IS_LIMIT_VERSION = 1 AND '||VERSION ||'>= MIN_VERSION AND '||VERSION || '<= MAX_VERSION))' ||
'AND IS_SHOW = 1 ' ||
'AND PS_ID IN ('''||PSIDS||''')';
  OPEN ENTRY_CURSOR FOR V_SQL;
  LOOP
    fetch ENTRY_CURSOR
      into CUR_PMID_TEMP;
       exit when ENTRY_CURSOR%NOTFOUND;
    PMIDS := PMIDS || CUR_PMID_TEMP || ',';
  END LOOP;
  CLOSE ENTRY_CURSOR;
  
  IF length(PMIDS) > 0 THEN
    PMIDS := substr(PMIDS,1,length(PMIDS)-1);
    PMIDS := ''||REPLACE(PMIDS, ',', ''',''')||'';
  END IF;
V_SQL :='SELECT O1.ID,O1.NAME,O1.TYPE,O1.URL,O1.ICON,O2.WEIGHT,O1.DESCRIPTION,O1.IS_SHOW,O1.CHANNEL_TYPE,O2.IS_DEFAULT FROM O_PAY_MODE O1,O_PAY_ENTRY O2 WHERE ' ||
'O1.ID=O2.PM_ID ' ||
'AND O1.IS_SHOW=1 ' ||
'AND O1.ID IN('''||PMIDS||''')';


OPEN V_RESULT FOR V_SQL;
END GET_PAYMODE;
/
